﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;

namespace UniMana.Data
{
    public static class Traning_TrainingProgram_DetailData
    {

        /// <summary>
        /// Lấy chi tiết chương trình đào tạo từ nhiều bảng
        /// </summary>
        /// <param name="nTrainingProgramDetail"></param>
        /// <param name="nTrainingSubject"></param>
        /// <param name="nTrainingSubjectGroup"></param>
        /// <param name="_programKey"></param>
        /// <returns></returns>
        public static IQueryable GetListMultiTable(this Table<Training_TrainingProgram_Detail> nTrainingProgramDetail,
                                            Table<Training_Subject> nTrainingSubject,
                                            Table<Training_SubjectGroup> nTrainingSubjectGroup,
                                            int _programKey)
        {
            var s = from m in nTrainingProgramDetail
                    join sj in nTrainingSubject on m.Training_SubjectKey equals sj.Training_SubjectKey
                    join sg in nTrainingSubjectGroup on m.Training_SubjectGroupKey equals sg.Training_SubjectGroupKey
                    where m.Training_TrainingProgramKey == _programKey
                    select new
                    {
                        m.Training_TrainingProgramKey,
                        m.Training_SubjectGroupKey,
                        GroupName = sg.Name,
                        sj.ID,
                        SubjectName = sj.Name
                    };
            return s;
        }

        /// <summary>
        /// Lấy chi tiết chương trình đào tạo từ nhiều bảng
        /// </summary>
        /// <param name="nTrainingProgramDetail"></param>
        /// <param name="nTrainingSubject"></param>
        /// <param name="nTrainingSubjectGroup"></param>
        /// <param name="_programKey"></param>
        /// <returns></returns>
        public static IQueryable GetListDetail(this Table<Training_TrainingProgram_Detail> nTrainingProgramDetail,
                                            Table<Training_Subject> nTrainingSubject,
                                            int _programKey)
        {
            var s = from m in nTrainingProgramDetail
                    join sj in nTrainingSubject on m.Training_SubjectKey equals sj.Training_SubjectKey
                    where m.Training_TrainingProgramKey == _programKey
                    select new
                    {
                        m.Training_TrainingProgramKey,
                        m.Catalogue_SubjectTypeKey,
                        //sj.CreditName1,
                        sj.Credits1,
                        //sj.CreditName2,
                        sj.Credits2,
                        //sj.CreditName3,
                        sj.Credits3,
                        m.Training_SubjectGroupKey,
                        m.Training_SubjectKey,
                        sj.ID,
                        sj.Name,
                        sj.Catalogue_DepartmentKey,
                        SubjectName = sj.Name
                    };
            return s;
        }

        /// <summary>
        /// Lấy chi tiết chương trình đào tạo theo khóa ngoại
        /// </summary>
        /// <param name="nTrainingProgramDetail"></param>
        /// <param name="_programKey"></param>
        /// <param name="_subjectKey"></param>
        /// <returns></returns>
        public static Training_TrainingProgram_Detail GetList(this Table<Training_TrainingProgram_Detail> nTrainingProgramDetail,
                                                             int _programKey, int _subjectKey)
        {
            Training_TrainingProgram_Detail result = (from trn_dt in nTrainingProgramDetail
                                                      where trn_dt.Training_TrainingProgramKey == _programKey
                                                      where trn_dt.Training_SubjectKey == _subjectKey
                                                      select trn_dt).FirstOrDefault();
            return result;

        }


        /// <summary>
        /// Lấy tất cả môn học trong chi tiết chương trình đào tạo
        /// </summary>
        /// <param name="nData"></param>
        /// <param name="_trainingProgramKey"></param>
        /// <returns></returns>
        public static IQueryable GetListDetailTrainingProramKey(DBMain nData, int _trainingProgramKey)
        {
            var allDetail = from t_tpd in nData.m_DCReadOnly.Training_TrainingProgram_Details
                            from t_s in nData.m_DCReadOnly.Training_Subjects
                            from t_sg in nData.m_DCReadOnly.Training_SubjectGroups
                            from c_st in nData.m_DCReadOnly.Catalogue_SubjectTypes
                            where
                            t_tpd.Training_TrainingProgramKey == _trainingProgramKey &&
                            t_tpd.Training_SubjectKey == t_s.Training_SubjectKey &&
                            t_tpd.Training_SubjectGroupKey == t_sg.Training_SubjectGroupKey &&
                            t_tpd.Catalogue_SubjectTypeKey == c_st.Catalogue_SubjectTypeKey
                            select new
                            {
                                t_tpd.Training_TrainingProgramKey,
                                t_tpd.Training_SubjectKey,

                                SubjectType = c_st.Name,
                                SubjectGroup = t_sg.Name,

                                SubjectID = t_s.ID,
                                SubjectName = t_s.Name,

                                TotalCredits = t_s.TotalCredits
                            };

            return allDetail;

        }
        public static IQueryable GetDetailNotSchedule(DBMain nData, int _trainingProgramKey)
        {

            var listNotSchedule = from p in
                                      (from t_tpd in nData.m_DCReadOnly.Training_TrainingProgram_Details
                                       from t_s in nData.m_DCReadOnly.Training_Subjects
                                       from t_sg in nData.m_DCReadOnly.Training_SubjectGroups
                                       from c_st in nData.m_DCReadOnly.Catalogue_SubjectTypes
                                       where
                                       t_tpd.Training_TrainingProgramKey == _trainingProgramKey &&
                                       t_tpd.Training_SubjectKey == t_s.Training_SubjectKey &&
                                       t_tpd.Training_SubjectGroupKey == t_sg.Training_SubjectGroupKey &&
                                       t_tpd.Catalogue_SubjectTypeKey == c_st.Catalogue_SubjectTypeKey
                                       select new
                                       {
                                           t_tpd.Training_TrainingProgramKey,
                                           t_tpd.Training_SubjectKey,

                                           SubjectType = c_st.Name,
                                           SubjectGroup = t_sg.Name,

                                           SubjectID = t_s.ID,
                                           SubjectName = t_s.Name,

                                           TotalCredits = t_s.TotalCredits
                                       })
                                  where !(from em in nData.m_DCReadOnly.Training_TrainingProgram_Schedules
                                          where em.Training_TrainingProgramKey == _trainingProgramKey
                                          select em.Training_SubjectKey).Contains(p.Training_SubjectKey)
                                  select p;

            return listNotSchedule;
        }
        public static IQueryable GetDetailInSchedule(DBMain nData, int _trainingProgramKey)
        {

            var listInSchedule = from p in
                                     (from t_tpd in nData.m_DCReadOnly.Training_TrainingProgram_Details
                                      from t_s in nData.m_DCReadOnly.Training_Subjects
                                      from t_sg in nData.m_DCReadOnly.Training_SubjectGroups
                                      from c_st in nData.m_DCReadOnly.Catalogue_SubjectTypes
                                      where
                                      t_tpd.Training_TrainingProgramKey == _trainingProgramKey &&
                                      t_tpd.Training_SubjectKey == t_s.Training_SubjectKey &&
                                      t_tpd.Training_SubjectGroupKey == t_sg.Training_SubjectGroupKey &&
                                      t_tpd.Catalogue_SubjectTypeKey == c_st.Catalogue_SubjectTypeKey
                                      select new
                                      {
                                          t_tpd.Training_TrainingProgramKey,
                                          t_tpd.Training_SubjectKey,

                                          SubjectType = c_st.Name,
                                          SubjectGroup = t_sg.Name,

                                          SubjectID = t_s.ID,
                                          SubjectName = t_s.Name,

                                          TotalCredits = t_s.TotalCredits
                                      })
                                 where (from em in nData.m_DCReadOnly.Training_TrainingProgram_Schedules
                                        where em.Training_TrainingProgramKey == _trainingProgramKey
                                        select em.Training_SubjectKey).Contains(p.Training_SubjectKey)
                                 select p;

            return listInSchedule;
        }

        #region Chi --Hòa
        /// <summary>
        /// Lấy danh sách môn học theo mã chương trình đào tạo
        /// </summary>
        /// <param name="nTrainingProgram_Detail"></param>
        /// <param name="nSubject"></param>
        /// <param name="_iTrainingProgramKey">Mã môn học</param>
        /// <returns></returns>
        public static IQueryable GetListByTrainingProgramKey(
            this Table<Training_TrainingProgram_Detail> nTrainingProgram_Detail,
            Table<Training_Subject> nSubject,
            int _iTrainingProgramKey,
            bool GetStatus = false)
        {
            var subjectList = from subject in nSubject
                              join detail in nTrainingProgram_Detail
                                          on subject.Training_SubjectKey
                                          equals detail.Training_SubjectKey
                              where detail.Training_TrainingProgramKey == _iTrainingProgramKey
                              select new
                              {
                                  subject.Training_SubjectKey,
                                  SubjectID = subject.ID,
                                  SubjectName = subject.Name,
                                  subject.Catalogue_DepartmentKey,
                                  detail.Training_TrainingProgramKey,
                                  subject.Credits1,
                                  subject.Credits2,
                                  subject.Credits3,
                                  subject.Credits4,
                                  subject.Credits5,
                                  TotalCredits = subject.Credits1 +
                                                 subject.Credits2 +
                                                 subject.Credits3 +
                                                 subject.Credits4 +
                                                 subject.Credits5,
                                  detail.Training_SubjectGroupKey,
                                  detail.Catalogue_SubjectTypeKey,
                                  subject.Subject_SameKey
                              };
            return subjectList;
        }
        #endregion
    }
}
